package com.bdqn.tour.dao.impl;


import com.bdqn.tour.dao.BaseDao;
import com.bdqn.tour.dao.HqfqueryScenicDao;
import com.bdqn.tour.pojo.Scenic;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

//根据城市查询城市景点信息
public class HqfqueryScenicDaoImpl extends BaseDao implements HqfqueryScenicDao {
    @Override
    public List<Scenic> getScenicList(String city) {
                //创建Scenic集合存储数据
                List<Scenic> list = new ArrayList<>();
                //获取数据库连接Connection
                Connection connection = getConnection();
                //初始化对象PreparedStatement，ResultSet
                PreparedStatement preparedStatement = null;
                ResultSet resultSet = null;

                try {
                    //编写sql语句，根据城市名称查询所有信息
                    String sql = "SELECT * FROM  scenictable where city = ?";
                    //创建预编译对象
                    preparedStatement = connection.prepareStatement(sql);
                    preparedStatement.setString(1, city);

                    //执行查询获取结果集
                    resultSet = preparedStatement.executeQuery();

                    //遍历结果集
                    while (resultSet.next()) {

                        int id = resultSet.getInt("scenic_Id");
                        String name = resultSet.getString("scenic_name");
                        String address = resultSet.getString("scenic_address");
                        int price = resultSet.getInt("price");
                        String img = resultSet.getString("img");
                         int satisfaction = resultSet.getInt("satisfaction");
                        String city1 = resultSet.getString("city");
                        String html = resultSet.getString("html");
                        //创建scenic对象
                        Scenic scenic = new Scenic(id,name,address, price, img,satisfaction,city1,html);
                        //将scenic对象添加到集合
                        list.add(scenic);
                    }
                } catch (SQLException e) {
                    e.printStackTrace();
                } finally {
                    closeAll (connection, preparedStatement, resultSet);
                }
                //返回scenic集合列表
                return list;
            }

    //根据景点名称查询相应景点信息
    @Override
    public List<Scenic> showScenicList(String scenic_name) {
        List<Scenic> list = new ArrayList<>();
        Connection conn = this.getConnection();
        PreparedStatement psmt = null;
        ResultSet rs = null;
        Scenic scenic =null;
            String sql = "SELECT scenic_Id, scenic_name, scenic_address, price, img from scenictable WHERE scenic_name LIKE ?";
        try {
            psmt= conn.prepareStatement(sql);
            psmt.setString(1,"%"+scenic_name+"%");
            rs = psmt.executeQuery();
        while (rs.next()){
            scenic = new Scenic();
            scenic.setScenic_Id(rs.getInt("scenic_Id"));
            scenic.setScenic_name(rs.getString("scenic_name"));
            scenic.setScenic_address(rs.getString("scenic_address"));
            scenic.setPrice(rs.getInt("price"));
            scenic.setImg(rs.getString("img"));
           list.add(scenic);
        }

        } catch (SQLException e) {
           e.printStackTrace();
        } finally {
            closeAll(conn,psmt,rs);
        }
        return list;
    }
}
